package DAO;

import POJO.Product;
import POJO.Productcat;
import Util.DataAccessUtil;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * @author Nguyen Hong Phu (1142066) - 11HCB (HCMUS)
 */
public class ProductDAO {
    
    
    public static List<Product> getListProducts(int productCatId) throws Exception{
        String hql = "SELECT p from Product p LEFT JOIN FETCH p.store "
                    +"LEFT JOIN FETCH p.unit "
                    +"LEFT JOIN FETCH p.productcat "
                    +"WHERE p.productcat.id = :productCatId";
       
        Map<String,Object> params = new HashMap<>();
        params.put("productCatId", productCatId);
        
        return DataAccessUtil.getList(hql,params);
    }
     public static boolean checkExistsProduct(String code) throws Exception{
        String hql = "SELECT p FROM Product p WHERE p.id = :code";
        Map<String,Object> params = new HashMap<>();
        params.put("code", code);
        return DataAccessUtil.getList(hql,params).size() > 0;
    }
     /**
      * List bán chạy
      * @param c
      * @return
      * @throws Exception 
      */
     public static List<Product> getListHotProducts(Productcat c) throws Exception{
        String hql = "SELECT p FROM Product p LEFT JOIN FETCH p.productcat c "
                    +"WHERE c.id = :idProductcat AND p.totalinstock != 0 AND p.quantity != 0 "
                    +"ORDER BY (p.totalinstock - p.quantity) DESC ";
        Map<String,Object> params = new HashMap<>();
        params.put("idProductcat", c.getId());
         return DataAccessUtil.getList(hql,params);
     }
     /**
      * List tồn kho
      * @param c
      * @return
      * @throws Exception 
      */
      public static List<Product> getListInventoryProducts(Productcat c) throws Exception{
        String hql = "SELECT p FROM Product p LEFT JOIN FETCH p.productcat c "
                    +"WHERE c.id = :idProductcat AND p.totalinstock != 0 "
                    +"ORDER BY (p.totalinstock - p.quantity) ASC ";
        Map<String,Object> params = new HashMap<>();
        params.put("idProductcat", c.getId());
         return DataAccessUtil.getList(hql,params);
     }
      /**
      * List hết hàng
      * @param c
      * @return
      * @throws Exception 
      */
      public static List<Product> getListSoldOutProducts(Productcat c) throws Exception{
        String hql = "SELECT p FROM Product p LEFT JOIN FETCH p.productcat c "
                    +"WHERE c.id = :idProductcat AND p.totalinstock != 0 AND  p.quantity = 0 ";
        Map<String,Object> params = new HashMap<>();
        params.put("idProductcat", c.getId());
         return DataAccessUtil.getList(hql,params);
     }
     /**
      * List gần hết hàng
      * @param c
      * @return
      * @throws Exception 
      */
     public static List<Product> getListNearlySoldOutProducts(Productcat c) throws Exception{
        String hql = "SELECT p FROM Product p LEFT JOIN FETCH p.productcat c "
                    +"WHERE c.id = :idProductcat AND p.totalinstock != 0 AND  p.quantity != 0 "
                    +"ORDER BY p.quantity ASC";
        Map<String,Object> params = new HashMap<>();
        params.put("idProductcat", c.getId());
         return DataAccessUtil.getList(hql,params);
     }
     /**
      * List chưa nhập hàng
      * @param c
      * @return
      * @throws Exception 
      */
     public static List<Product> getListNotWarehouseProducts(Productcat c) throws Exception{
        String hql = "SELECT p FROM Product p LEFT JOIN FETCH p.productcat c "
                    +"WHERE c.id = :idProductcat AND p.totalinstock = 0 "
                    +"ORDER BY p.id DESC";
        Map<String,Object> params = new HashMap<>();
        params.put("idProductcat", c.getId());
         return DataAccessUtil.getList(hql,params);
     }
}
